/*
 * Copyright 2020-2021 redragon.dongbin
 *
 * This file is part of redragon-erp/赤龙ERP.

 * redragon-erp/赤龙ERP is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 2 of the License, or
 * (at your option) any later version.

 * redragon-erp/赤龙ERP is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.

 * You should have received a copy of the GNU General Public License
 * along with redragon-erp/赤龙ERP.  If not, see <https://www.gnu.org/licenses/>.
 */
package com.erp.hr.dao.hibernate;

import com.erp.hr.dao.HrStaffDepartmentRDao;
import com.erp.hr.dao.model.*;
import org.apache.commons.lang.StringUtils;
import org.redragon.framework.annotation.Permissions;
import org.redragon.framework.annotation.Permissions.PermissionType;
import org.redragon.framework.annotation.SqlParam;
import org.redragon.framework.hibernate.DaoSupport;
import org.redragon.framework.hibernate.model.Pages;
import org.redragon.framework.util.DaoCRUDUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@Repository
public class HrStaffDepartmentRDaoImpl implements HrStaffDepartmentRDao{ 

    //注入DaoSupport工具类
    @Autowired
    private DaoSupport daoSupport;
    
    @Override
    public void insertDataObject(HrStaffDepartmentR obj) {
        this.daoSupport.insertDataTransaction(obj);
    }

    @Override
    public void updateDataObject(HrStaffDepartmentR obj) {
        this.daoSupport.updateDataTransaction(obj);
    }
    
    @Override
    public void insertOrUpdateDataObject(HrStaffDepartmentR obj) {
        this.daoSupport.insertOrUpdateDataTransaction(obj);
    }

    @Override
    public void deleteDataObject(HrStaffDepartmentR obj) {
        this.daoSupport.deleteDataTransactionJPA(obj);
    }

    @Override
    public List<HrStaffDepartmentR> getDataObjects() {
        return this.daoSupport.getDataAllObject(HrStaffDepartmentR.class);
    }

    @Override
    public HrStaffDepartmentR getDataObject(int id) {
        return (HrStaffDepartmentR)this.daoSupport.getDataObject(HrStaffDepartmentR.class, id);
    }
    
    @Override
    public HrStaffDepartmentR getDataObject(String code) {
        return null;
    }
    
    @Override
    public List<HrStaffDepartmentR> getDataObjects(HrStaffDepartmentRCO paramObj) {
        String sql = "select p.* from hr_staff_department_r p where 1=1";
        
        Map<String, Object> args = new HashMap<String, Object>();
        sql = sql + DaoCRUDUtil.getSQLSearch(paramObj, "departmentCode", "and p.", args);
        sql = sql + DaoCRUDUtil.getSQLSearch(paramObj, "positionCode", "and p.", args);
        
        Map<String, Class<?>> entity = new HashMap<String, Class<?>>();
        entity.put("p", HrStaffDepartmentR.class);
        
        return this.daoSupport.selectDataSql(sql, entity, args);
    }
    
    @Override
    public List<HrStaffDepartmentR> getDataObjects(Pages pages) {
        return null;
    }
    
    @Override
    public List<HrStaffDepartmentR> getDataObjects(Pages pages, HrStaffDepartmentRCO paramObj) {
        return null;
    }

    @Override
    public List<Map<String, Object>> getDataObjectsArray(Pages pages, HrStaffDepartmentRCO paramObj) {
        return null;
    }
    
    @Override
    @Permissions(PermissionType.OWN)
    public List<HrStaffDepartmentR> getDataObjectsForDataAuth(@SqlParam String dataAuthSQL, Pages pages, HrStaffDepartmentRCO paramObj) {
        return null;
    }
    
    @Override
    public List getHrStaffDepartmentRList(Pages pages, HrStaffDepartmentRCO paramObj) {
        String sql = "select r.*,s.*,p.*,d.* from hr_staff_department_r r,hr_staff s,hr_position p,hr_department d "
                + "where r.staff_code = s.staff_code and r.position_code = p.position_code and r.department_code = d.department_code";
        
        Map<String, Object> args = new HashMap<String, Object>();
        sql = sql + DaoCRUDUtil.getSQLSearch(paramObj, "departmentId", "and d.", args);
        sql = sql + " order by s.staff_code";
        
        Map<String, Class<?>> entity = new LinkedHashMap<String, Class<?>>();
        entity.put("r", HrStaffDepartmentR.class);
        entity.put("s", HrStaff.class);
        entity.put("p", HrPosition.class);
        entity.put("d", HrDepartment.class);
        
        return this.daoSupport.getDataSqlByPage(sql, entity, args, pages);
    }
    @Override
    public List<HrStaffDepartmentR> getStaffListByDepartment(String departmentCode) {
        String sql = "select r.* from hr_staff_department_r r "
                + "where r.status='Y' and r.department_code in (select department_code from hr_department h "
                + "where h.status='Y' and h.segment_code like "
                + "(select CONCAT(d.segment_code,'%') from hr_department d where d.department_code = :departmentCode))";

        Map<String, Object> args = new HashMap<String, Object>();
        args.put("departmentCode", departmentCode);

        Map<String, Class<?>> entity = new HashMap<String, Class<?>>();
        entity.put("r", HrStaffDepartmentR.class);

        return this.daoSupport.selectDataSql(sql, entity, args);
    }


    @Override
    public List<HrStaffDepartmentVO> getStaffByLader(HrStaffDepartmentR department) {
        String sql = "SELECT h.*,'1' id,'1' sd_id,g.position_code,g.position_name,g.position_type from ( "
                +"select f.staff_code,g.position_code,g.position_name,g.position_type from "
                +"(SELECT * from hr_staff_department_r e where e.department_code in("
                +"SELECT c.parent_department_code "
                +"from hr_staff a "
                +"INNER JOIN hr_staff_department_r b on a.staff_code=b.staff_code "
                +"LEFT JOIN hr_department c on b.department_code=c.department_code "
                +"where a.staff_code=:staff_code)) f "
                +"LEFT JOIN hr_position g on f.position_code=g.position_code) g "
                +"LEFT JOIN hr_staff h on g.staff_code=h.staff_code ";
        Map<String, Object> args = new HashMap<String, Object>();
        args.put("staff_code", department.getStaffCode());

        if(department != null && StringUtils.isNotEmpty(department.getPositionCode()) ){
            sql = sql + DaoCRUDUtil.getSQLSearch(department, "position_code", "and g.", args);
        }

        return this.daoSupport.selectDataSql(sql, HrStaffDepartmentVO.class, args);
    }

    @Override
    public List<HrStaffDepartmentVO> getStaffByPosition(String departmentCode,String positionId) {
        String sql = "SELECT '1' as id,'1' as sd_id,'1' as staff_entery_date,'1' as username,'1' as org_code,"
        + "'1' as company_code,'1' as company_name,"
        + "a.staff_id,a.staff_tel,"
        + "a.staff_code,a.staff_name,"
        + "a.staff_number,a.staff_sex,a.staff_status,a.staff_mobile,a.staff_email,a.created_date,"
        + "a.created_by,a.last_updated_date,a.last_updated_by,"
        +"b.position_code,c.department_code,c.department_name,d.position_type,d.position_code,d.position_name from "
        +"hr_staff a,"
        +"hr_staff_department_r b,"
        +"hr_department c,"
        +"hr_position d  where a.staff_code=b.staff_code and b.department_code=c.department_code and b.position_code=d.position_code "
        +"and b.position_code=:positionId and c.department_code=:departmentCode";
        Map<String, Object> args = new HashMap<String, Object>();
        args.put("positionId", positionId);
        args.put("departmentCode", departmentCode);
        return this.daoSupport.selectDataSql(sql, HrStaffDepartmentVO.class, args);
    }

    @Override
    public List<HrStaffDepartmentVO> getStaffByTopPosition(String departmentCode, String positionId) {
        HrDepartment hrDepartment=getHrDepartmentParentCompany(departmentCode);
        if(hrDepartment==null){return null;}
        String sql = "SELECT  '1' as id,'1' as sd_id,'1' as staff_entery_date,'1' as username,"
                +"'1' as org_code,'1' as company_code,'1' as company_name,"
                +"a.staff_code,b.staff_id,b.staff_number,b.staff_sex,a.department_code,a.position_code,"
                +"b.staff_status,b.staff_mobile,b.staff_email,"
                +"b.created_date,b.created_by,b.last_updated_date,b.last_updated_by,b.staff_tel,"
                +"b.staff_name,c.department_name,"
                +"c.parent_department_code,d.position_name,d.position_type "
                +"from hr_staff_department_r a,hr_staff b,hr_department c,hr_position d "
                +"where a.staff_code=b.staff_code and a.department_code=c.department_code and "
                +"a.position_code=d.position_code "
                +"and a.department_code=:departmentCode and d.position_code=:positionId";
        Map<String, Object> args = new HashMap<String, Object>();
        args.put("departmentCode", hrDepartment.getDepartmentCode());
        args.put("positionId", positionId);
        return this.daoSupport.selectDataSql(sql, HrStaffDepartmentVO.class, args);
    }
    @Override
    public HrDepartment getHrDepartmentParentCompany(String departmentCode) {
        if(StringUtils.isEmpty(departmentCode)){return null;}
        String sql="SELECT b.* from hr_department b,(select a.parent_department_code from " +
                "hr_department a where a.department_code=:departmentCode) c where " +
                "b.department_code=c.parent_department_code";
        Map<String, Object> args = new HashMap<String, Object>();
        args.put("departmentCode", departmentCode);
        Map<String, Class<?>> entity = new HashMap<String, Class<?>>();
        entity.put("a", HrDepartment.class);
        List<HrDepartment> depInfo=daoSupport.selectDataSql(sql, entity, args);
        if(depInfo==null ||depInfo.size()==0){
            return null;
        }
        HrDepartment hrDepartment=depInfo.get(0);
        //判断是否是该级部门所在的上级公司
        if("0".equals(hrDepartment.getIscompany())){
            return hrDepartment;
        }
        else{
            return getHrDepartmentParentCompany(hrDepartment.getDepartmentCode());
        }
    }

    @Override
    public List<HrStaffDepartmentVO> getStaffByDepartment(String departmentCode) {
        String sql = "SELECT '1' as id,'1' as sd_id,'1' as staff_entery_date,'1' as username,'1' as org_code,"
                + "'1' as company_code,'1' as company_name,"
                + "a.staff_id,a.staff_tel,"
                + "a.staff_code,a.staff_name,"
                + "a.staff_number,a.staff_sex,a.staff_status,a.staff_mobile,a.staff_email,a.created_date,"
                + "a.created_by,a.last_updated_date,a.last_updated_by,"
                +"b.position_code,c.department_code,c.department_name,'1' as position_type,'1' as position_code,'1' as position_name from "
                +"hr_staff a,"
                +"hr_staff_department_r b,"
                +"hr_department c "
                +"where a.staff_code=b.staff_code and b.department_code=c.department_code "
                +"and c.department_code=:departmentCode";
        Map<String, Object> args = new HashMap<String, Object>();
        args.put("departmentCode", departmentCode);
        return this.daoSupport.selectDataSql(sql, HrStaffDepartmentVO.class, args);
    }

    /**
     * 根据岗位编码获取总部所有职员集合
     * @param postCode 岗位编码
     * @return 职员集合
     */
    @Override
    public List<HrStaff> getAllStaffByPostCode(String postCode) {
        return daoSupport.selectDataSql(
                "SELECT " +
                        "hs.* " +
                    "FROM " +
                        "hr_staff hs " +
                    "WHERE " +
                        "hs.staff_code IN ( " +
                            "SELECT " +
                                "staff_code " +
                            "FROM " +
                                "hr_staff_department_r " +
                            "WHERE " +
                                "department_code IN ( " +
                                    "SELECT " +
                                        "department_code " +
                                    "FROM " +
                                        "hr_department " +
                                    "WHERE " +
                                        "iscompany IN ('0', '1') " +
                                ") " +
                            "AND " +
                                "position_code = :post_code " +
                        ")",
                new HashMap<String, Class<?>>(){{
                    put("hs", HrStaff.class);
                }},
                new HashMap<String, Object>(){{
                    put("post_code", postCode);
                }});
    }

    /**
     * 根据岗位编码获取分公司所有职员集合
     * @param companyCode 分公司编码
     * @param postCode 岗位编码
     * @return 职员集合
     */
    @Override
    public List<HrStaff> getAllStaffByPostCode(String companyCode, String postCode) {
        return daoSupport.selectDataSql(
                "SELECT " +
                        "hs.* " +
                    "FROM " +
                        "hr_staff hs " +
                    "WHERE " +
                        "hs.staff_code IN ( " +
                            "SELECT " +
                                "staff_code " +
                            "FROM " +
                                "hr_staff_department_r " +
                            "WHERE " +
                                "department_code IN ( " +
                                    "SELECT " +
                                        "department_code " +
                                    "FROM " +
                                        "hr_department " +
                                    "WHERE " +
                                        "segment_code LIKE ( " +
                                            "SELECT " +
                                                "CONCAT(segment_code, '%') " +
                                            "FROM " +
                                                "hr_department " +
                                            "WHERE " +
                                                "department_code = :company_code " +
                                        ")  " +
                                    "AND " +
                                        "iscompany IN ('2', '3') " +
                                ") " +
                            "AND " +
                                "position_code = :post_code " +
                        ")",
                new HashMap<String, Class<?>>(){{
                    put("hs", HrStaff.class);
                }},
                new HashMap<String, Object>(){{
                    put("company_code", companyCode);
                    put("post_code", postCode);
                }});
    }

}
